--网点
  with yesterday_network_data as(
  select
     customer_code,            --客户编码
     customer_name,            --客户名字
     ordersource_code,          --平台ID
     ordersource_name,          --平台名字
     customer_category,
     customer_category_name,
	 customer_type_id,
	 customer_type_name,
	 customer_attribute_id,
	 customer_attribute_name,
	 subordinate_agent_name,
     subordinate_agent_id,
     subordinate_agent_code,
     first_franchisee_id,
     first_franchisee_code,
     first_franchisee_name,
     customer_belong_network_id,
     customer_belong_network_name,
     customer_province_id,
     customer_province_desc,
     customer_city_id,
     customer_city_desc,
     customer_area_id,
	 customer_area_desc,
     day_customer_sum as yesterday_customer_waybill_sum,   --客户发货运单量
     customer_signed_time
  from jms_dm.dm_customer_base_detail_dt
  where dt =date_add('{{ execution_date | cst_ds }}',-1)
  and day_customer_sum>0
  --group by customer_code,ordersource_name
  ),

  --今日数据
  today_customer as(
  select
      nvl(a.customer_code,b.customer_code) as customer_code,                                                   --客户编码
      nvl(a.customer_name,b.customer_name) as customer_name,                                                   --客户名字
      case when nvl(a.day_customer_sum,0) >5000 then 'A'
           when nvl(a.day_customer_sum,0) >1000 then 'B'
           when nvl(a.day_customer_sum,0) >200 then 'C'
           when nvl(a.day_customer_sum,0) >50 then 'D'
           else 'E' end  as customer_level,                                                                        --客户等级
       case when nvl(b.yesterday_customer_waybill_sum,0) >5000 then 'A'
            when nvl(b.yesterday_customer_waybill_sum,0) >1000 then 'B'
            when nvl(b.yesterday_customer_waybill_sum,0) >200 then 'C'
            when nvl(b.yesterday_customer_waybill_sum,0) >50 then 'D'
            else 'E' end  as yesterday_customer_level,                                                             --昨日客户等级
      nvl(a.ordersource_code,b.ordersource_code) as ordersource_code,                                              --平台ID
      nvl(a.ordersource_name,b.ordersource_name) as ordersource_name,                                              --平台名字
      nvl(a.customer_type_id,b.customer_type_id) as customer_type_id  ,                                            -- 客户类型
      nvl(a.customer_type_name,b.customer_type_name) as customer_type_name,                                        --客户类型名字
      nvl(a.customer_category,b.customer_category) as customer_category,                                           --客户类别
      nvl(a.customer_category_name,b.customer_category_name) as customer_category_name,
      nvl(a.customer_attribute_id,b.customer_attribute_id) as customer_attribute_id ,                              --客户属性ID
      nvl(a.customer_attribute_name,b.customer_attribute_name) as customer_attribute_name,                         --客户属性名字
      nvl(a.subordinate_agent_name,b.subordinate_agent_name) as subordinate_agent_name,                            --所属代理区
      nvl(a.subordinate_agent_id,b.subordinate_agent_id) as subordinate_agent_id,                                  --所属代理区ID
      nvl(a.subordinate_agent_code, b.subordinate_agent_code) as subordinate_agent_code,                           --所属代理区code
      nvl(a.first_franchisee_id,b.first_franchisee_id) as first_franchisee_id,                                     -- 一级加盟商id',
      nvl(a.first_franchisee_code,b.first_franchisee_code) as first_franchisee_code,                               -- '一级加盟商code',
      nvl(a.first_franchisee_name,b.first_franchisee_name) as first_franchisee_name,                               -- 一级加盟商',
      nvl(a.customer_belong_network_id,b.customer_belong_network_id) as customer_belong_network_id ,               -- 客户所属网点ID
      nvl(a.customer_belong_network_name,b.customer_belong_network_name) as customer_belong_network_name,          --  客户所属网点名字
      nvl(a.customer_province_id,b.customer_province_id) as customer_province_id,                                  -- 客户所属省份id
      nvl(a.customer_province_desc,b.customer_province_desc) as customer_province_desc,                            --客户所属省份名字
      nvl(a.customer_city_id,b.customer_city_id) as customer_city_id,                                              -- 客户所属城市id
      nvl(a.customer_city_desc,b.customer_city_desc) as customer_city_desc,                                        --  客户所属城市名字',
      nvl(a.customer_area_id,b.customer_area_id) as customer_area_id,                                              --  客户所属区域id
      nvl(a.customer_area_desc,b.customer_area_desc) as customer_area_desc,                                        -- 客户所属区县的名字',
      nvl(a.day_customer_sum,0) as day_customer_sum,                                                               --当日运单总量
      nvl(b.yesterday_customer_waybill_sum,0) as yesterday_customer_sum,                                                               --昨日运单总量
      nvl(a.customer_signed_time,b.customer_signed_time) as customer_signed_time,                                  --客户创建时间
  if(a.customer_signed_time='{{ execution_date | cst_ds }}' and nvl(a.day_customer_sum,0)>0 ,1,0)as is_new,                                   --是否为新增
  if(a.customer_signed_time='{{ execution_date | cst_ds }}' and nvl(a.day_customer_sum,0)>0 ,nvl(a.day_customer_sum,0) ,0)as new_customer_waybill_sum,   --新增客户运单总量
  case when   a.ordersource_name is null  and b.ordersource_name is not null then 1
       else 0 end as is_loss,                                                                                      --是否流失
  case when  a.ordersource_name is null  and b.ordersource_name is not null then b.yesterday_customer_waybill_sum
     else 0 end as loss_waybill_sum,                                                                               --流失运单总量
 case when a.customer_signed_time<'{{ execution_date | cst_ds }}' and a.ordersource_name is not null  and b.ordersource_name is null then 1
       else 0 end as is_retrieve,                                                                                  --是否挽回
  case when a.customer_signed_time<'{{ execution_date | cst_ds }}' and a.ordersource_name is not null  and b.ordersource_name is null then a.day_customer_sum
     else 0 end as retrieve_waybill_sum                                                                           --挽回运单总量
  from (select *
         from jms_dm.dm_customer_base_detail_dt
         where dt='{{ execution_date | cst_ds }}'
         )  a
  full join  yesterday_network_data b
  on a.customer_code=b.customer_code
  and a.ordersource_name=b.ordersource_name
  where  (a.ordersource_name is not null or b.ordersource_name is not null)
  ),
 network_customer as(
 select
             'network' as org_level,
             'day' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,   --代理区名字
             today.first_franchisee_code,    --加盟商code
             today.first_franchisee_name,    --加盟商名字
             today.customer_belong_network_id,  --客户所属网点ID
             today.customer_belong_network_name,  --客户所属网点名字
             today.customer_cnt,    --发件客户个数
             today_sum.today_customer_cnt,                    --今日总的发件客户个数
             round(today.customer_cnt/ today_sum.today_customer_cnt,4)  as customer_cnt_rate,  --客户占比
             yesterday.yesterday_network_customer_cnt,           --昨日客户个数
             today.day_customer_sum,--发件客户运单总量
             today_sum.today_day_customer_sum,                    --今日客户运单总量
             round(today.day_customer_sum/today_sum.today_day_customer_sum,4)  as customer_waybill_rate,  --客户运单占比
             round(today.customer_cnt/yesterday.yesterday_network_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.yesterday_network_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.yesterday_network_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.yesterday_network_customer_cnt,4) as retrieve_customer_rate  --挽回户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,   --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,   --代理区名字
             first_franchisee_code,                --加盟商code
             max(first_franchisee_name) as first_franchisee_name,        --加盟商名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             sum( if(day_customer_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(day_customer_sum,0))  as day_customer_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from today_customer a
            -- where a.day_customer_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id
      )today
  left join (
           select
           customer_belong_network_id,       --客户所属网点ID
           max(customer_belong_network_name) as customer_belong_network_name,   --客户所属网点名字
           ordersource_name,                --平台
           yesterday_customer_level,
           count( distinct customer_code) as yesterday_network_customer_cnt
           from today_customer
           where yesterday_customer_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt =date_add('{{ execution_date | cst_ds }}',-1)
--           and day_customer_sum>0
           group by customer_belong_network_id,ordersource_name,yesterday_customer_level
        )yesterday
on today.customer_belong_network_id=yesterday.customer_belong_network_id
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.yesterday_customer_level
join (
select

             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             count( distinct customer_code) as today_customer_cnt,    --发件客户个数
             sum(day_customer_sum)  as  today_day_customer_sum         --今日发件客户总单量
             from today_customer a
             where a.day_customer_sum>0
             group by ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id
) today_sum
on today.customer_belong_network_id=today_sum.customer_belong_network_id
and today.ordersource_name=today_sum.ordersource_name
 ),
 network_customer_sum as (
 select
             'network' as org_level,
             'day' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code))ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,   --代理区名字
             first_franchisee_code,    --加盟商code
             max(first_franchisee_name) as first_franchisee_name,    --加盟商名字
             customer_belong_network_id,  --客户所属网点ID
             max(customer_belong_network_name) as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客户个数
             max(today_customer_cnt) as today_customer_cnt,                    --今日总的发件客户个数
              1 as customer_cnt_rate,  --客户占比
             sum(yesterday_network_customer_cnt) as yesterday_network_customer_cnt,           --昨日客户个数
             sum(day_customer_sum) as day_customer_sum,--发件客户运单总量
             max(today_day_customer_sum) as today_day_customer_sum,                    --今日客户运单总量
             1 as customer_waybill_rate,  --客户运单占比
             round(sum(customer_cnt)/sum(yesterday_network_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
              sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(yesterday_network_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(yesterday_network_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(yesterday_network_customer_cnt),4) as retrieve_customer_rate  --挽回户率
 from network_customer
 group by ordersource_name,subordinate_agent_code,first_franchisee_code,customer_belong_network_id

 ),
 --加盟商
 fran_customer as(
          select
             'fran' as org_level,
             'day' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --代理区code
             today.subordinate_agent_name,   --代理区名字
             today.first_franchisee_code,  --客户所属加盟商code
             today.first_franchisee_name,  --客户所属加盟商名字
             null as customer_belong_network_id,    --网点id
             null as customer_belong_network_name,   --网点名字
             today.customer_cnt,    --发件客户个数
             today_sum.today_customer_cnt,                    --今日总的发件客户个数
             round(today.customer_cnt/ today_sum.today_customer_cnt,4)  as customer_cnt_rate,  --客户占比
             yesterday.yesterday_network_customer_cnt,           --昨日客户个数
             today.day_customer_sum,--发件客户运单总量
             today_sum.today_day_customer_sum,
             round(today.day_customer_sum/today_sum.today_day_customer_sum,4)  as customer_waybill_rate,  --客户运单占比
             round(today.customer_cnt/yesterday.yesterday_network_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户运单总量
             round(today.new_cnt/yesterday.yesterday_network_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.yesterday_network_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum ,  --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.yesterday_network_customer_cnt,4) as retrieve_customer_rate  --挽回客户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code))ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,
              max(subordinate_agent_name) as subordinate_agent_name,
             first_franchisee_code,  --客户所加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --客户所属网点名字
             sum(if(day_customer_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(day_customer_sum,0))  as day_customer_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from today_customer a
             --where a.day_customer_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code,first_franchisee_code
      )today
  left join (
           select
           first_franchisee_code,       --客户所属加盟商code
           max(first_franchisee_name) as first_franchisee_name,   --客户所属加盟商名字
           ordersource_name,             --平台code
           yesterday_customer_level,
           count( distinct customer_code) as yesterday_network_customer_cnt
           from today_customer
           where yesterday_customer_sum>0
           group by first_franchisee_code,ordersource_name,yesterday_customer_level
        )yesterday
on today.first_franchisee_code=yesterday.first_franchisee_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.yesterday_customer_level
join (
select
             concat_ws(',',collect_set(ordersource_code))ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             first_franchisee_code,  --客户所属加盟商code
             max(first_franchisee_name) as first_franchisee_name,  --客户所属加盟商名字
             count( distinct customer_code) as today_customer_cnt,    --发件客户个数
            sum(day_customer_sum)  as  today_day_customer_sum
             from today_customer a
             where a.day_customer_sum>0
             group by ordersource_name,subordinate_agent_code,first_franchisee_code
) today_sum
on today.first_franchisee_code=today_sum.first_franchisee_code
and today.ordersource_code=today_sum.ordersource_code
 ),
   fran_customer_sum as (
 select
             'fran' as org_level,
             'day' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,   --代理区名字
             first_franchisee_code,    --加盟商code
             max(first_franchisee_name) as first_franchisee_name,    --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as  customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客户个数
             max(today_customer_cnt) as today_customer_cnt,                    --今日总的发件客户个数
              1 as customer_cnt_rate,  --客户占比
             sum(yesterday_network_customer_cnt) as yesterday_network_customer_cnt,           --昨日客户个数
             sum(day_customer_sum) as day_customer_sum,--发件客户运单总量
             max(today_day_customer_sum) as today_day_customer_sum,                    --今日客户运单总量
             1 as customer_waybill_rate,  --客户运单占比
             round(sum(customer_cnt)/sum(yesterday_network_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
              sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(yesterday_network_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(yesterday_network_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(yesterday_network_customer_cnt),4) as retrieve_customer_rate  --挽回户率
 from fran_customer
 group by ordersource_name,subordinate_agent_code,first_franchisee_code),

--代理区
agent_customer as(
          select
             'agent' as org_level,
             'day' as date_type,
             today.customer_level,           --客户等级
             today.ordersource_code,         --客户平台ID
             today.ordersource_name,  --平台名字
             today.subordinate_agent_code,  --客户所属代理区code
             today.subordinate_agent_name,  --客户所属代理区名字
             null as first_franchisee_code,  --客户所属加盟商code
             null as first_franchisee_name,  --客户所属加盟商名字
             null as customer_belong_network_id,    --网点id
             null as customer_belong_network_name,   --网点名字
             today.customer_cnt,    --发件客户个数
             today_sum.today_customer_cnt,                    --今日总的发件客户个数
             round(today.customer_cnt/ today_sum.today_customer_cnt,4)  as customer_cnt_rate,  --客户占比
             yesterday.yesterday_network_customer_cnt,           --昨日客户个数
             today.day_customer_sum,--发件客户运单总量
             today_sum.today_day_customer_sum,
             round(today.day_customer_sum/today_sum.today_day_customer_sum,4)  as customer_waybill_rate,  --客户运单占比
             round(today.customer_cnt/yesterday.yesterday_network_customer_cnt-1,4) as customer_up_rate,  --客户提升率
             today.new_cnt,      --新增客户数
             today.new_customer_waybill_sum,  --新增客户总量
             round(today.new_cnt/yesterday.yesterday_network_customer_cnt,4) as new_customer_rate,  --新增客户率
             today.loss_cnt,   --流失客户数
             today.loss_waybill_sum,  --流失客户运单总量
             round(today.loss_cnt/yesterday.yesterday_network_customer_cnt,4) as loss_customer_rate,  --流失客户率
             today.retrieve_cnt,  --挽回客户数
             today.retrieve_waybill_sum,   --挽回客户运单总量
             round(today.retrieve_cnt/yesterday.yesterday_network_customer_cnt,4) as retrieve_customer_rate  --流失客户率
from (
             select
             customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --客户所加盟商code
             max(subordinate_agent_name) as subordinate_agent_name,  --客户所代理区名字
             sum( if(day_customer_sum>0,1,0)) as customer_cnt,    --发件客数
             sum(nvl(day_customer_sum,0))  as day_customer_sum,  --发件客户运单总量
             sum(is_new) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户重量
             sum(is_loss)  as  loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             sum(is_retrieve)  as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum   --挽回客户运单总量
             from today_customer a
             --where a.day_customer_sum>0
             group by customer_level,ordersource_name,subordinate_agent_code
      )today
  left join (
           select
           subordinate_agent_code,       --客户所属代理区code
           max(subordinate_agent_name) as subordinate_agent_name,   --客户所属代理区名字
           ordersource_name,             --平台code
           yesterday_customer_level,
           count( distinct customer_code) as yesterday_network_customer_cnt
           from today_customer
           where yesterday_customer_sum>0
--           from jms_dm.dm_customer_base_detail_dt
--           where dt =date_add('{{ execution_date | cst_ds }}',-1)
--           and day_customer_sum>0
           group by subordinate_agent_code,ordersource_name,yesterday_customer_level
               )yesterday
on today.subordinate_agent_code=yesterday.subordinate_agent_code
and today.ordersource_name=yesterday.ordersource_name
and today.customer_level=yesterday.yesterday_customer_level
join (
select
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,                                        --客户平台ID
             ordersource_name,               --平台名字
             subordinate_agent_code,                                  --客户所代理区code
             max(subordinate_agent_name) as subordinate_agent_name,   --客户所属代理区名字
             count( distinct customer_code) as today_customer_cnt,    --发件客户个数
             sum(day_customer_sum)  as  today_day_customer_sum        --今日代理区总单量
             from today_customer a
             where a.day_customer_sum>0
             group by ordersource_name,subordinate_agent_code
) today_sum
on today.subordinate_agent_code=today_sum.subordinate_agent_code
and today.ordersource_name=today_sum.ordersource_name
),
agent_customer_sum as (
select
             'agent' as org_level,
             'day' as date_type,
             '汇总' as customer_level,           --客户等级
             concat_ws(',',collect_set(ordersource_code)) as ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --代理区code
             max(subordinate_agent_name) as subordinate_agent_name,   --代理区名字
             null as first_franchisee_code,    --加盟商code
             null as  first_franchisee_name,    --加盟商名字
             null as customer_belong_network_id,  --客户所属网点ID
             null as customer_belong_network_name,  --客户所属网点名字
             sum(customer_cnt) as customer_cnt,    --发件客户个数
             max(today_customer_cnt) as today_customer_cnt,                    --今日总的发件客户个数
              1 as customer_cnt_rate,  --客户占比
             sum(yesterday_network_customer_cnt) as yesterday_network_customer_cnt,           --昨日客户个数
             sum(day_customer_sum) as day_customer_sum,--发件客户运单总量
             max(today_day_customer_sum) as today_day_customer_sum,                    --今日客户运单总量
             1 as customer_waybill_rate,  --客户运单占比
             round(sum(customer_cnt)/sum(yesterday_network_customer_cnt)-1,4) as customer_up_rate,  --客户提升率
              sum(new_cnt) as new_cnt,      --新增客户数
             sum(new_customer_waybill_sum) as new_customer_waybill_sum,  --新增客户运单总量
             round(sum(new_cnt)/sum(yesterday_network_customer_cnt),4) as new_customer_rate,  --新增客户率
             sum(loss_cnt) as loss_cnt,   --流失客户数
             sum(loss_waybill_sum) as loss_waybill_sum,  --流失客户运单总量
             round(sum(loss_cnt)/sum(yesterday_network_customer_cnt),4) as loss_customer_rate,  --流失客户率
             sum(retrieve_cnt) as retrieve_cnt,  --挽回客户数
             sum(retrieve_waybill_sum) as retrieve_waybill_sum,   --挽回客户运单总量
             round(sum(retrieve_cnt)/sum(yesterday_network_customer_cnt),4) as retrieve_customer_rate  --挽回户率
 from agent_customer
 group by ordersource_name,subordinate_agent_code
)
insert overwrite table jms_dm.dm_customer_changes_day_dt partition(dt,org_level)
 select
             date_type,
             customer_level,           --客户等级
             ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,
             subordinate_agent_name,
             first_franchisee_code,
             first_franchisee_name,
             customer_belong_network_id,  --客户所属网点ID
             customer_belong_network_name,  --客户所属网点名字
             customer_cnt,    --发件客数
             today_customer_cnt,
             customer_cnt_rate,
             yesterday_network_customer_cnt,
             day_customer_sum,--发件客户运单总量
             today_day_customer_sum,
             customer_waybill_rate,
             customer_up_rate,  --客户提升率
             new_cnt,      --新增客户数
             new_customer_waybill_sum,  --新增客户运单总量
             new_customer_rate,  --新增客户率
             loss_cnt,   --流失客户数
             loss_waybill_sum,  --流失客户运单总量
             loss_customer_rate,   --流失率
             retrieve_cnt,  --挽回客户数
             retrieve_waybill_sum,   --挽回客户运单总量
             retrieve_customer_rate,  --挽回率
             '{{ execution_date | cst_ds }}' as dt2,
             org_level as org_level2,
             '{{ execution_date | cst_ds }}' as dt,
              org_level
from (
select
            org_level,
                        date_type,
                        customer_level,           --客户等级
                        ordersource_code,         --客户平台ID
                        ordersource_name,  --平台名字
                        subordinate_agent_code,  --客户所属代理区code
                        subordinate_agent_name,  --客户所属代理区名字
                        first_franchisee_code,  --客户所属加盟商code
                        first_franchisee_name,  --客户所属加盟商名字
                        customer_belong_network_id,    --网点id
                        customer_belong_network_name,   --网点名字
                        customer_cnt,    --发件客户个数
                        today_customer_cnt,                    --今日总的发件客户个数
                        customer_cnt_rate,  --客户占比
                        yesterday_network_customer_cnt,           --昨日客户个数
                        day_customer_sum,--发件客户运单总量
                        today_day_customer_sum,
                        customer_waybill_rate,  --客户运单占比
                        customer_up_rate,  --客户提升率
                        new_cnt,      --新增客户数
                        new_customer_waybill_sum,  --新增客户总量
                        new_customer_rate,  --新增客户率
                        loss_cnt,   --流失客户数
                        loss_waybill_sum,  --流失客户运单总量
                        loss_customer_rate,  --流失客户率
                        retrieve_cnt,  --挽回客户数
                        retrieve_waybill_sum,   --挽回客户运单总量
                        retrieve_customer_rate  --流失客户率
from network_customer
union all
select
            org_level,
                        date_type,
                        customer_level,           --客户等级
                        ordersource_code,         --客户平台ID
                        ordersource_name,  --平台名字
                        subordinate_agent_code,  --客户所属代理区code
                        subordinate_agent_name,  --客户所属代理区名字
                        first_franchisee_code,  --客户所属加盟商code
                        first_franchisee_name,  --客户所属加盟商名字
                        customer_belong_network_id,    --网点id
                        customer_belong_network_name,   --网点名字
                        customer_cnt,    --发件客户个数
                        today_customer_cnt,                    --今日总的发件客户个数
                        customer_cnt_rate,  --客户占比
                        yesterday_network_customer_cnt,           --昨日客户个数
                        day_customer_sum,--发件客户运单总量
                        today_day_customer_sum,
                        customer_waybill_rate,  --客户运单占比
                        customer_up_rate,  --客户提升率
                        new_cnt,      --新增客户数
                        new_customer_waybill_sum,  --新增客户总量
                        new_customer_rate,  --新增客户率
                        loss_cnt,   --流失客户数
                        loss_waybill_sum,  --流失客户运单总量
                        loss_customer_rate,  --流失客户率
                        retrieve_cnt,  --挽回客户数
                        retrieve_waybill_sum,   --挽回客户运单总量
                        retrieve_customer_rate  --流失客户率
from network_customer_sum

union all
select
            org_level,
                        date_type,
                        customer_level,           --客户等级
                        ordersource_code,         --客户平台ID
                        ordersource_name,  --平台名字
                        subordinate_agent_code,  --客户所属代理区code
                        subordinate_agent_name,  --客户所属代理区名字
                        first_franchisee_code,  --客户所属加盟商code
                        first_franchisee_name,  --客户所属加盟商名字
                        customer_belong_network_id,    --网点id
                        customer_belong_network_name,   --网点名字
                        customer_cnt,    --发件客户个数
                        today_customer_cnt,                    --今日总的发件客户个数
                        customer_cnt_rate,  --客户占比
                        yesterday_network_customer_cnt,           --昨日客户个数
                        day_customer_sum,--发件客户运单总量
                        today_day_customer_sum,
                        customer_waybill_rate,  --客户运单占比
                        customer_up_rate,  --客户提升率
                        new_cnt,      --新增客户数
                        new_customer_waybill_sum,  --新增客户总量
                        new_customer_rate,  --新增客户率
                        loss_cnt,   --流失客户数
                        loss_waybill_sum,  --流失客户运单总量
                        loss_customer_rate,  --流失客户率
                        retrieve_cnt,  --挽回客户数
                        retrieve_waybill_sum,   --挽回客户运单总量
                        retrieve_customer_rate  --流失客户率
from fran_customer
union all
select
            org_level,
                        date_type,
                        customer_level,           --客户等级
                        ordersource_code,         --客户平台ID
                        ordersource_name,  --平台名字
                        subordinate_agent_code,  --客户所属代理区code
                        subordinate_agent_name,  --客户所属代理区名字
                        first_franchisee_code,  --客户所属加盟商code
                        first_franchisee_name,  --客户所属加盟商名字
                        customer_belong_network_id,    --网点id
                        customer_belong_network_name,   --网点名字
                        customer_cnt,    --发件客户个数
                        today_customer_cnt,                    --今日总的发件客户个数
                        customer_cnt_rate,  --客户占比
                        yesterday_network_customer_cnt,           --昨日客户个数
                        day_customer_sum,--发件客户运单总量
                        today_day_customer_sum,
                        customer_waybill_rate,  --客户运单占比
                        customer_up_rate,  --客户提升率
                        new_cnt,      --新增客户数
                        new_customer_waybill_sum,  --新增客户总量
                        new_customer_rate,  --新增客户率
                        loss_cnt,   --流失客户数
                        loss_waybill_sum,  --流失客户运单总量
                        loss_customer_rate,  --流失客户率
                        retrieve_cnt,  --挽回客户数
                        retrieve_waybill_sum,   --挽回客户运单总量
                        retrieve_customer_rate  --流失客户率
from fran_customer_sum
    union all
select
               org_level,
             date_type,
             customer_level,           --客户等级
             ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --客户所属代理区code
             subordinate_agent_name,  --客户所属代理区名字
             first_franchisee_code,  --客户所属加盟商code
             first_franchisee_name,  --客户所属加盟商名字
             customer_belong_network_id,    --网点id
             customer_belong_network_name,   --网点名字
             customer_cnt,    --发件客户个数
             today_customer_cnt,                    --今日总的发件客户个数
             customer_cnt_rate,  --客户占比
             yesterday_network_customer_cnt,           --昨日客户个数
             day_customer_sum,--发件客户运单总量
             today_day_customer_sum,
             customer_waybill_rate,  --客户运单占比
             customer_up_rate,  --客户提升率
             new_cnt,      --新增客户数
             new_customer_waybill_sum,  --新增客户总量
             new_customer_rate,  --新增客户率
             loss_cnt,   --流失客户数
             loss_waybill_sum,  --流失客户运单总量
             loss_customer_rate,  --流失客户率
             retrieve_cnt,  --挽回客户数
             retrieve_waybill_sum,   --挽回客户运单总量
             retrieve_customer_rate  --流失客户率
from agent_customer
 union all
select
               org_level,
             date_type,
             customer_level,           --客户等级
             ordersource_code,         --客户平台ID
             ordersource_name,  --平台名字
             subordinate_agent_code,  --客户所属代理区code
             subordinate_agent_name,  --客户所属代理区名字
             first_franchisee_code,  --客户所属加盟商code
             first_franchisee_name,  --客户所属加盟商名字
             customer_belong_network_id,    --网点id
             customer_belong_network_name,   --网点名字
             customer_cnt,    --发件客户个数
             today_customer_cnt,                    --今日总的发件客户个数
             customer_cnt_rate,  --客户占比
             yesterday_network_customer_cnt,           --昨日客户个数
             day_customer_sum,--发件客户运单总量
             today_day_customer_sum,
             customer_waybill_rate,  --客户运单占比
             customer_up_rate,  --客户提升率
             new_cnt,      --新增客户数
             new_customer_waybill_sum,  --新增客户总量
             new_customer_rate,  --新增客户率
             loss_cnt,   --流失客户数
             loss_waybill_sum,  --流失客户运单总量
             loss_customer_rate,  --流失客户率
             retrieve_cnt,  --挽回客户数
             retrieve_waybill_sum,   --挽回客户运单总量
             retrieve_customer_rate  --流失客户率
from agent_customer_sum

) a
distribute by 10;
